package sqlutil;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table;

import sqlutil.bean.Task;
import sqlutil.bean.TaskGroup;


public class SqlQuery {
	
	
	public static void main(String[] args) throws Exception {
		SqlQuery q = new SqlQuery();
		q.addClass( Task.class );
		q.select(" t_task");
		System.out.println("dfsfsfdf");
		q.add(Restrictions.eq("taskname", "人物名"));
		q.add( Restrictions.eq("taskGroup.id", "id"));
		System.out.println( q.sql() );
	}
	
	Class<?> mainclass ;
	String mainTable ;
	String mainId;
	
	final List<String> resultCol = new ArrayList<String>();	//返回结果集
	
	final List<Criteria> criterias = new ArrayList<Criteria>();	//条件集合
	
	final List<Object> params = new ArrayList<Object>();	//sql参数集合
	
	public final Map<String,String> alias = new HashMap<String,String>();	//别名map
	
	/**
	 * 主类中，有关联关系的字段 和 字段类定义表名 的映射
	 * key:关联表名
	 * value:关联字段
	 */
	Map<String,Field> relTableMap = new HashMap<String, Field>();	
	
	//装载主Class类
	public SqlQuery addClass( Class<?> mainclass ) throws Exception{
		this.mainclass = mainclass; 
		Table table = mainclass.getAnnotation(javax.persistence.Table.class);
		mainTable = table.name();
		
		Field[] fields = mainclass.getDeclaredFields();
		for (Field field : fields) {
			Id id = field.getAnnotation( javax.persistence.Id.class );
			if( id != null){
				Column idcol = field.getAnnotation( javax.persistence.Column.class );
				if(idcol != null){
					mainId = idcol.name();
				}else{
					mainId = field.getName();
				}
			}
			
			ManyToMany m2m = field.getAnnotation( javax.persistence.ManyToMany.class );
			Annotation m2o = field.getAnnotation( javax.persistence.ManyToOne.class );
			Annotation o2m = field.getAnnotation( javax.persistence.OneToMany.class );
			Annotation o2o = field.getAnnotation( javax.persistence.OneToOne.class );
			if( m2m !=null || m2o !=null || o2m!=null || o2o!=null){
				Table t = field.getType().getAnnotation(javax.persistence.Table.class);
				if(t==null){
					throw new Exception("关联对象没定义表名");
				}
				relTableMap.put(t.name(), field);
			}
		}
		
		return this;
	}
	
	//指定返回结果集 
	public SqlQuery select( String tablename ){
		//还需要判断很多情况，目前一种
		if( !resultCol.contains(tablename) ){
			resultCol.add(tablename+".*");
		}
		return this;
	}
	
	//add限制
	public SqlQuery add( Criteria re ){
		criterias.add(re);
		return this;
	}
	
	StringBuilder sb = new StringBuilder();
	StringBuilder joinCondSb = new StringBuilder();
	
	//生成带？的sql语句
	public String sql() throws Exception{
		
		sb.append( "SELECT ");
		//和select()方法呼应
		//根据返回结果集，生成关联条件，比如inner join，left join
		
		for (int i = 0; i < resultCol.size(); i++) {
			String result = resultCol.get(i);	//目前表现为 xxxx.*
			
			//根据表名计算join条件
			int t = result.indexOf(".");
			if( t != -1){
				String tname = result.substring(0, t);
				if( relTableMap.containsKey( tname )){ //有关联表
					Field relField = relTableMap.get(tname);
					addToJoinCond( generateJoin(relField) );
				}
			}
			
			sb.append(result);
			if(i<resultCol.size()-1){
				sb.append(",");
			}
			
		}
		
		sb.append(" FROM ").append(mainTable);
		sb.append(joinCondSb).append(" ");	//加入join条件
		sb.append("WHERE 1=1 ");
		
		//解析where条件
		for (int i = 0; i < criterias.size(); i++) { 
			Criteria c = criterias.get(i);
			String tsql = c.toSqlString(this);
			sb.append( " AND "+tsql );
			params.addAll( c.getParamObjs() );
			addToJoinCond ( generateJoinByWhere(c) );
		}
		
		return sb.toString();
	}
	
	/**
	 * 根据PropertyName生成join条件，
	 * 比如group.id = ?，要根据主类与group的关系，生成join条件。
	 * 且要支持级联，如group.project.id=?
	 * @param c
	 * @throws Exception 
	 */
	private String generateJoinByWhere(Criteria c) throws Exception{
		StringBuilder sb = new StringBuilder();
		String[] propertyArr = c.getPropertyName().split(".");
		if( propertyArr.length <= 1 ){
			return "";
		}
		//
		List<Field> tfields = new ArrayList<Field>(propertyArr.length-1);
		for (int i = 0; i < propertyArr.length-1; i++) {
			String property = propertyArr[i];
			if( i== 0 ){	//用主类和当前property 关联
				Field relField = mainclass.getField(property);
				sb.append( generateJoin(relField) );
				tfields.add(relField);
			}else{	//用当前property 和 前一个property 关联
				Class<?> tmainClass = tfields.get(i-1).getType();
				Field relField = tmainClass.getField(property);
				sb.append( generateJoin(tmainClass, relField) );  
				tfields.add(relField) ;
			}
		}
		return sb.toString();
	}
	
	private Set<String> joinTables = new HashSet<String>();
	/**
	 * 把生成的join条件加入到总join条件中，
	 * 
	 * 需要判断 总join条件中 是否已经有cond条件，如果已经有了则不添加。
	 * @param cond
	 */
	private void addToJoinCond( String cond ){
		if( joinCondSb.toString().contains( cond ) ){	//joinCondSb中 包括这段语句，跳出
			return ;
		}
		Pattern p = Pattern.compile("join (.*?) on",Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher( cond );
		while( m.find() ){
			String jointable = m.group();
			if( joinTables.contains( jointable) ){
				continue;
			}else{
				joinCondSb.append( cond );
				joinTables.add( jointable );
			}
		}
	}
	
	/**
	 * 根据字段field 生成 join条件
	 * @param mainField
	 * @param relField
	 * @return
	 * @throws Exception
	 */
	private String generateJoin(Class<?> main1class, Field relField) throws Exception{
		StringBuilder sb = new StringBuilder();
		dd(sb, relField, main1class );
		return sb.toString();
	} 
	
	
	/**
	 * 根据字段field与主类， 生成 join条件
	 * @param relField
	 * @return
	 * @throws Exception
	 */
	private String generateJoin(Field relField) throws Exception{
		StringBuilder sb = new StringBuilder();
		dd(sb, relField, mainclass);
		return sb.toString();
	} 
	
	private void dd( StringBuilder sb, Field relField, Class<?> mainClass1 ) throws Exception{
		String mainFieldTable = mainClass1.getAnnotation( Table.class ).name(); 
		String mainFieldId = getIdAnnotation(mainClass1);
		
		ManyToMany m2m = relField.getAnnotation( javax.persistence.ManyToMany.class );
		if( m2m !=null){	//多对多
			JoinTable jt = relField.getAnnotation( javax.persistence.JoinTable.class );
			String zhongjianbiao = jt.name();
			JoinColumn joinColumn = null ;
			JoinColumn inverseJoinColumn = null ;
			if( jt.joinColumns().length > 0 ){
				joinColumn = jt.joinColumns()[0];
			} 
			if( jt.inverseJoinColumns().length > 0 ){
				inverseJoinColumn = jt.inverseJoinColumns()[0];
			}
			
			Table relTable = relField.getType().getAnnotation( Table.class );
			if( relTable==null){
				throw new Exception("关联对象没定义表名");
			}
			String relTableName = relTable.name();
			String relId = getIdAnnotation(relField);
			
			sb.append(" LEFT JOIN ").append(zhongjianbiao).append(" ON ").append( mainFieldTable ).append(".").append( mainFieldId )
			.append("=").append(zhongjianbiao).append(".").append( joinColumn.name() );
			
			sb.append(" LEFT JOIN ").append(zhongjianbiao).append(" ON ").append( relTableName ).append(".").append( relId )
			.append("=").append(zhongjianbiao).append(".").append( inverseJoinColumn.name() );
			
		}
		
		ManyToOne m2o = relField.getAnnotation( javax.persistence.ManyToOne.class );
		OneToMany o2m = relField.getAnnotation( javax.persistence.OneToMany.class );
		OneToOne o2o = relField.getAnnotation( javax.persistence.OneToOne.class );
		
	}
	
	/**
	 * 获得类的@Id的值
	 * @param relclass
	 * @return
	 */
	private String getIdAnnotation( Class<?> relclass ){
		for (Field field : relclass.getFields() ) {
			Id id = field.getAnnotation( javax.persistence.Id.class );
			if( id != null){
				Column idcol = field.getAnnotation( javax.persistence.Column.class );
				if(idcol != null){
					return idcol.name();
				}else{
					return field.getName();
				}
			}
		}
		return null;
	}
	
	/**
	 * 获得关联字段所在类的@Id的值
	 * @param relField
	 * @return
	 */
	private String getIdAnnotation( Field relField ){
		return getIdAnnotation(relField.getType());
	}
}
